const multer = require('multer');
const path = require('path');
const fs = require('fs').promises;
const { v4: uuidv4 } = require('uuid');
const { logger } = require('../utils/logger');
const MenuImportService = require('../services/MenuImportService');
const MenuExportService = require('../services/MenuExportService');
const ImportJob = require('../models/ImportJob');
const { query: dbQuery } = require('../config/database');

// 配置文件上传
const storage = multer.diskStorage({
  destination: async (req, file, cb) => {
    const uploadDir = path.join(process.cwd(), 'uploads', 'menu_imports');
    await fs.mkdir(uploadDir, { recursive: true });
    cb(null, uploadDir);
  },
  filename: (req, file, cb) => {
    const uniqueName = `${Date.now()}_${uuidv4()}_${file.originalname}`;
    cb(null, uniqueName);
  }
});

const upload = multer({
  storage,
  limits: {
    fileSize: 10 * 1024 * 1024, // 10MB
    files: 1
  },
  fileFilter: (req, file, cb) => {
    const allowedTypes = [
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      'application/vnd.ms-excel'
    ];
    
    if (allowedTypes.includes(file.mimetype) || file.originalname.endsWith('.xlsx')) {
      cb(null, true);
    } else {
      cb(new Error('只支持 .xlsx 格式的Excel文件'), false);
    }
  }
});

/**
 * 菜单导入导出控制器
 */
class MenuImportController {
  constructor() {
    this.importService = new MenuImportService();
    this.exportService = new MenuExportService();
  }

  /**
   * 下载菜单导入模板
   * GET /admin/template/menu.xlsx
   */
  async downloadTemplate(req, res) {
    try {
      logger.info('用户请求下载菜单模板', { 
        user_id: req.user?.id, 
        ip: req.ip 
      });

      const template = await this.exportService.generateTemplate();
      
      // 记录审计日志
      await this.logAuditAction(req, 'template_download', {
        template_type: 'menu',
        filename: template.filename
      }, 'success');

      res.setHeader('Content-Type', template.contentType);
      res.setHeader('Content-Disposition', `attachment; filename="${template.filename}"`);
      res.setHeader('Content-Length', template.buffer.length);
      
      res.send(template.buffer);
    } catch (error) {
      logger.error('下载模板失败:', error);
      
      await this.logAuditAction(req, 'template_download', {
        template_type: 'menu',
        error: error.message
      }, 'failed');
      
      res.status(500).json({
        success: false,
        message: '模板生成失败',
        error: error.message
      });
    }
  }

  /**
   * 导出菜单数据
   * GET /admin/export/menu
   */
  async exportMenu(req, res) {
    try {
      const filters = {
        category_id: req.query.category_id ? parseInt(req.query.category_id) : undefined,
        category_name: req.query.category_name,
        created_after: req.query.created_after,
        created_before: req.query.created_before,
        is_available: req.query.is_available !== undefined ? req.query.is_available === 'true' : undefined
      };

      logger.info('用户请求导出菜单数据', { 
        user_id: req.user?.id, 
        ip: req.ip,
        filters 
      });

      // 验证导出参数
      const validationErrors = this.exportService.validateExportParams(filters);
      if (validationErrors.length > 0) {
        return res.status(400).json({
          success: false,
          message: '导出参数验证失败',
          errors: validationErrors
        });
      }

      const exportResult = await this.exportService.exportMenuData(filters);
      
      // 记录审计日志
      await this.logAuditAction(req, 'menu_export', {
        filters,
        record_count: exportResult.recordCount,
        filename: exportResult.filename
      }, 'success');

      res.setHeader('Content-Type', exportResult.contentType);
      res.setHeader('Content-Disposition', `attachment; filename="${exportResult.filename}"`);
      res.setHeader('Content-Length', exportResult.buffer.length);
      
      res.send(exportResult.buffer);
    } catch (error) {
      logger.error('导出菜单数据失败:', error);
      
      await this.logAuditAction(req, 'menu_export', {
        filters: req.query,
        error: error.message
      }, 'failed');
      
      res.status(500).json({
        success: false,
        message: '导出失败',
        error: error.message
      });
    }
  }

  /**
   * 上传并执行Dry-Run
   * POST /admin/import/menu
   */
  async uploadAndDryRun(req, res) {
    const uploadMiddleware = upload.single('file');
    
    uploadMiddleware(req, res, async (err) => {
      if (err) {
        logger.error('文件上传失败:', err);
        return res.status(400).json({
          success: false,
          message: '文件上传失败',
          error: err.message
        });
      }

      if (!req.file) {
        return res.status(400).json({
          success: false,
          message: '请选择要上传的Excel文件'
        });
      }

      try {
        logger.info('用户上传菜单导入文件', { 
          user_id: req.user?.id, 
          ip: req.ip,
          filename: req.file.originalname,
          size: req.file.size
        });

        // 创建导入任务
        const job = await ImportJob.create({
          job_id: uuidv4(),
          admin_id: req.user.id,
          file_path: req.file.path,
          file_name: req.file.originalname,
          file_size: req.file.size,
          import_type: 'menu',
          status: 'uploaded'
        });

        // 执行Dry-Run
        const dryRunResult = await this.importService.performDryRun(job);
        
        // 记录审计日志
        await this.logAuditAction(req, 'menu_import_dryrun', {
          job_id: job.job_id,
          filename: req.file.originalname,
          total_rows: dryRunResult.total_rows,
          valid_rows: dryRunResult.valid_rows,
          error_rows: dryRunResult.error_rows
        }, dryRunResult.error_rows === 0 ? 'success' : 'warning');

        const response = {
          success: true,
          message: 'Dry-Run 验证完成',
          job_id: job.job_id,
          statistics: {
            total_rows: dryRunResult.total_rows,
            valid_rows: dryRunResult.valid_rows,
            error_rows: dryRunResult.error_rows,
            warning_rows: dryRunResult.warning_rows,
            new_records: dryRunResult.new_records,
            updated_records: dryRunResult.updated_records
          },
          can_commit: dryRunResult.error_rows === 0,
          has_warnings: dryRunResult.warning_rows > 0
        };

        // 如果有错误，提供错误文件下载链接
        if (dryRunResult.error_file_path) {
          response.error_download_url = `/admin/import/errors/${job.job_id}`;
        }

        res.json(response);
      } catch (error) {
        logger.error('Dry-Run执行失败:', error);
        
        await this.logAuditAction(req, 'menu_import_dryrun', {
          filename: req.file?.originalname,
          error: error.message
        }, 'failed');
        
        // 清理上传的文件
        if (req.file?.path) {
          await fs.unlink(req.file.path).catch(() => {});
        }
        
        res.status(500).json({
          success: false,
          message: 'Dry-Run 执行失败',
          error: error.message
        });
      }
    });
  }

  /**
   * 提交导入数据
   * POST /admin/import/menu/commit
   */
  async commitImport(req, res) {
    try {
      const { job_id } = req.query;
      
      if (!job_id) {
        return res.status(400).json({
          success: false,
          message: '缺少 job_id 参数'
        });
      }

      logger.info('用户请求提交导入数据', { 
        user_id: req.user?.id, 
        ip: req.ip,
        job_id 
      });

      // 查找导入任务
      const job = await ImportJob.findByJobId(job_id);
      if (!job) {
        return res.status(404).json({
          success: false,
          message: '导入任务不存在'
        });
      }

      // 验证任务状态
      if (job.status !== 'validated') {
        return res.status(400).json({
          success: false,
          message: `任务状态不允许提交，当前状态: ${job.status}`
        });
      }

      // 验证用户权限
      if (job.user_id !== req.user.id && !req.user.roles?.includes('admin')) {
        return res.status(403).json({
          success: false,
          message: '无权限操作此导入任务'
        });
      }

      // 检查Dry-Run结果
      const dryRunResult = job.dry_run_result;
      if (!dryRunResult || dryRunResult.error_rows > 0) {
        return res.status(400).json({
          success: false,
          message: 'Dry-Run 验证未通过，存在错误数据'
        });
      }

      // 执行提交
      const commitResult = await this.importService.commitImport(job);
      
      // 记录审计日志
      await this.logAuditAction(req, 'menu_import_commit', {
        job_id: job.job_id,
        processed_records: commitResult.processed_records
      }, 'success');

      res.json({
        success: true,
        message: '导入数据提交成功',
        job_id: job.job_id,
        processed_records: commitResult.processed_records
      });
    } catch (error) {
      logger.error('提交导入数据失败:', error);
      
      await this.logAuditAction(req, 'menu_import_commit', {
        job_id: req.query.job_id,
        error: error.message
      }, 'failed');
      
      res.status(500).json({
        success: false,
        message: '提交失败',
        error: error.message
      });
    }
  }

  /**
   * 查询导入任务状态
   * GET /admin/import/job/:id
   */
  async getJobStatus(req, res) {
    try {
      const { id } = req.params;
      
      logger.info('用户查询导入任务状态', { 
        user_id: req.user?.id, 
        ip: req.ip,
        job_id: id 
      });

      const job = await ImportJob.findByJobId(id);
      if (!job) {
        return res.status(404).json({
          success: false,
          message: '导入任务不存在'
        });
      }

      // 验证用户权限
      if (job.user_id !== req.user.id && !req.user.roles?.includes('admin')) {
        return res.status(403).json({
          success: false,
          message: '无权限查看此导入任务'
        });
      }

      const response = {
        success: true,
        job: {
          job_id: job.job_id,
          status: job.status,
          import_type: job.import_type,
          original_filename: job.original_filename,
          file_size: job.file_size,
          created_at: job.created_at,
          updated_at: job.updated_at,
          dry_run_result: job.dry_run_result,
          can_rollback: job.canRollback(),
          has_snapshot: !!job.snapshot_data
        }
      };

      // 如果有错误文件，提供下载链接
      if (job.dry_run_result?.error_file_path) {
        response.job.error_download_url = `/admin/import/errors/${job.job_id}`;
      }

      res.json(response);
    } catch (error) {
      logger.error('查询任务状态失败:', error);
      
      res.status(500).json({
        success: false,
        message: '查询失败',
        error: error.message
      });
    }
  }

  /**
   * 下载错误文件
   * GET /admin/import/errors/:job_id
   */
  async downloadErrors(req, res) {
    try {
      const { job_id } = req.params;
      
      logger.info('用户下载错误文件', { 
        user_id: req.user?.id, 
        ip: req.ip,
        job_id 
      });

      const job = await ImportJob.findByJobId(job_id);
      if (!job) {
        return res.status(404).json({
          success: false,
          message: '导入任务不存在'
        });
      }

      // 验证用户权限
      if (job.user_id !== req.user.id && !req.user.roles?.includes('admin')) {
        return res.status(403).json({
          success: false,
          message: '无权限下载此错误文件'
        });
      }

      const errorFilePath = job.dry_run_result?.error_file_path;
      if (!errorFilePath) {
        return res.status(404).json({
          success: false,
          message: '错误文件不存在'
        });
      }

      // 检查文件是否存在
      const fileExists = await fs.access(errorFilePath).then(() => true).catch(() => false);
      if (!fileExists) {
        return res.status(404).json({
          success: false,
          message: '错误文件已被清理'
        });
      }

      const filename = `errors_${job_id}.csv`;
      
      res.setHeader('Content-Type', 'text/csv; charset=utf-8');
      res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
      
      const fileBuffer = await fs.readFile(errorFilePath);
      res.send(fileBuffer);
    } catch (error) {
      logger.error('下载错误文件失败:', error);
      
      res.status(500).json({
        success: false,
        message: '下载失败',
        error: error.message
      });
    }
  }

  /**
   * 获取导入任务列表
   * GET /admin/import/jobs
   */
  async getJobList(req, res) {
    try {
      const page = parseInt(req.query.page) || 1;
      const limit = parseInt(req.query.limit) || 20;
      const status = req.query.status;
      const import_type = req.query.import_type || 'menu';
      
      logger.info('用户查询导入任务列表', { 
        user_id: req.user?.id, 
        ip: req.ip,
        page, limit, status, import_type
      });

      const result = await ImportJob.findByAdmin({
        page,
        limit,
        status,
        import_type
      });

      res.json({
        success: true,
        data: result.jobs,
        pagination: {
          page,
          limit,
          total: result.total,
          pages: Math.ceil(result.total / limit)
        }
      });
    } catch (error) {
      logger.error('查询任务列表失败:', error);
      
      res.status(500).json({
        success: false,
        message: '查询失败',
        error: error.message
      });
    }
  }

  /**
   * 获取可用分类列表
   * GET /admin/import/categories
   */
  async getCategories(req, res) {
    try {
      const categories = await this.exportService.getAvailableCategories();
      
      res.json({
        success: true,
        data: categories
      });
    } catch (error) {
      logger.error('获取分类列表失败:', error);
      
      res.status(500).json({
        success: false,
        message: '获取分类列表失败',
        error: error.message
      });
    }
  }

  /**
   * 记录审计日志
   */
  async logAuditAction(req, action, data, status) {
    try {
      await dbQuery(`
        INSERT INTO audit_logs (
          user_id, action, request_data, response_data, 
          ip_address, user_agent, status, created_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
      `, [
        req.user?.id || null,
        action,
        JSON.stringify({
          method: req.method,
          url: req.originalUrl,
          params: req.params,
          query: req.query,
          ...data
        }),
        JSON.stringify({ status }),
        req.ip,
        req.get('User-Agent'),
        status
      ]);
    } catch (error) {
      logger.error('记录审计日志失败:', error);
    }
  }

  // ==================== V2 API Methods ====================
  
  /**
   * 下载V2菜单导入模板（新三表结构）
   */
  async downloadTemplateV2(req, res) {
    try {
      logger.info('开始下载V2菜单导入模板');
      
      // 检查feature flag
      if (!process.env.IMPORT_V2 || process.env.IMPORT_V2 !== 'true') {
        return res.status(404).json({
          success: false,
          message: 'V2功能未启用'
        });
      }

      const templatePath = await this.importService.generateTemplateV2();
      
      res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      res.setHeader('Content-Disposition', 'attachment; filename="menu_template_v2.xlsx"');
      
      // 使用res.download确保浏览器触发下载
      res.download(templatePath, 'cmb_menu_template_v2.xlsx', (err) => {
        if (err) {
          logger.error('发送模板文件失败:', err);
          if (!res.headersSent) {
            res.status(500).json({
              success: false,
              message: '下载模板失败',
              error: err.message
            });
          }
        }
      });
      
      await this.logAuditAction(req, 'download_template_v2', {}, 'success');
      logger.info('V2菜单导入模板下载完成');
      
      // 清理临时文件
      try {
        await fs.unlink(templatePath);
      } catch (cleanupError) {
        logger.warn('清理临时模板文件失败:', cleanupError);
      }
    } catch (error) {
      logger.error('下载V2菜单导入模板失败:', error);
      await this.logAuditAction(req, 'download_template_v2', { error: error.message }, 'error');
      res.status(500).json({
        success: false,
        message: '下载模板失败',
        error: error.message
      });
    }
  }

  /**
   * 导出V2菜单数据（新三表结构）
   */
  async exportMenuV2(req, res) {
    try {
      logger.info('开始导出V2菜单数据');
      
      // 检查feature flag
      if (!process.env.IMPORT_V2 || process.env.IMPORT_V2 !== 'true') {
        return res.status(404).json({
          success: false,
          message: 'V2功能未启用'
        });
      }

      const filters = {
        category_id: req.query.category_id,
        category_name: req.query.category_name,
        created_after: req.query.created_after,
        created_before: req.query.created_before,
        is_available: req.query.is_available
      };

      const filePath = await this.exportService.exportMenuV2(filters);
      
      res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      res.setHeader('Content-Disposition', `attachment; filename="menu_export_v2_${Date.now()}.xlsx"`);
      
      const fileStream = require('fs').createReadStream(filePath);
      fileStream.pipe(res);
      
      await this.logAuditAction(req, 'export_menu_v2', { filters }, 'success');
      logger.info('V2菜单数据导出完成');
    } catch (error) {
      logger.error('导出V2菜单数据失败:', error);
      await this.logAuditAction(req, 'export_menu_v2', { error: error.message }, 'error');
      res.status(500).json({
        success: false,
        message: '导出菜单失败',
        error: error.message
      });
    }
  }

  /**
   * V2菜单数据验证（新三表结构）
   */
  async validateV2(req, res) {
    const requestId = req.headers['x-request-id'] || 'req_' + Date.now();
    res.setHeader('X-Request-Id', requestId);
    
    upload.single('file')(req, res, async (err) => {
      if (err) {
        logger.error(`[${requestId}] 文件上传失败:`, err);
        return res.status(400).json({
          success: false,
          phase: 'validate',
          message: '文件上传失败',
          summary: { inserted: 0, updated: 0, skipped: 0, error_count: 1 },
          errors: [{
            sheet: 'File',
            row_number: 0,
            field: 'file',
            value: req.file?.originalname || 'unknown',
            error_code: err.code === 'LIMIT_FILE_SIZE' ? 'file_too_large' : 'upload_error',
            message: err.message
          }],
          warnings: []
        });
      }

      if (!req.file) {
        return res.status(400).json({
          success: false,
          phase: 'validate',
          message: '请选择要上传的文件',
          summary: { inserted: 0, updated: 0, skipped: 0, error_count: 1 },
          errors: [{
            sheet: 'File',
            row_number: 0,
            field: 'file',
            value: '',
            error_code: 'file_required',
            message: '请选择要上传的文件'
          }],
          warnings: []
        });
      }

      try {
        logger.info(`[${requestId}] 开始V2菜单数据验证`);
        
        // 检查feature flag
        if (!process.env.IMPORT_V2 || process.env.IMPORT_V2 !== 'true') {
          return res.status(404).json({
            success: false,
            phase: 'validate',
            message: 'V2功能未启用',
            summary: { inserted: 0, updated: 0, skipped: 0, error_count: 1 },
            errors: [{
              sheet: 'System',
              row_number: 0,
              field: 'feature_flag',
              value: process.env.IMPORT_V2 || 'undefined',
              error_code: 'feature_disabled',
              message: 'V2功能未启用'
            }],
            warnings: []
          });
        }

        const job = await ImportJob.create({
          import_type: 'menu_v2',
          original_filename: req.file.originalname,
          file_path: req.file.path,
          file_size: req.file.size,
          status: 'uploaded',
          request_id: requestId
        });

        // 异步执行验证
        setImmediate(async () => {
          try {
            const result = await this.importService.validateV2(job);
            logger.info(`[${requestId}] V2验证完成，任务ID: ${job.job_id}，结果: ${result.status}`);
          } catch (error) {
            logger.error(`[${requestId}] V2验证失败，任务ID: ${job.job_id}:`, error);
            await job.updateStatus('failed', { error_message: error.message });
          }
        });

        // 立即返回任务信息
        res.json({
          success: true,
          job_id: job.job_id,
          validation_result: {
            status: 'processing',
            message: '正在验证中，请稍后查询结果'
          }
        });

        await this.logAuditAction(req, 'validate_v2', { job_id: job.job_id, request_id: requestId }, 'success');
      } catch (error) {
        logger.error(`[${requestId}] V2菜单数据验证失败:`, error);
        await this.logAuditAction(req, 'validate_v2', { error: error.message, request_id: requestId }, 'error');
        res.status(500).json({
          success: false,
          phase: 'validate',
          message: '验证失败',
          summary: { inserted: 0, updated: 0, skipped: 0, error_count: 1 },
          errors: [{
            sheet: 'System',
            row_number: 0,
            field: 'validation',
            value: '',
            error_code: 'system_error',
            message: error.message
          }],
          warnings: []
        });
      }
    });
  }

  /**
   * 提交V2验证通过的数据（新三表结构）
   */
  async commitV2(req, res) {
    const requestId = req.headers['x-request-id'] || 'req_' + Date.now();
    res.setHeader('X-Request-Id', requestId);
    
    try {
      const { job_id } = req.query; // 统一从query取
      const { idempotency_key } = req.body || {};
      
      if (!job_id) {
        return res.status(400).json({
          success: false,
          phase: 'commit',
          message: 'job_id参数缺失',
          summary: { inserted: 0, updated: 0, skipped: 0, error_count: 1 },
          errors: [{
            sheet: 'System',
            row_number: 0,
            field: 'job_id',
            value: '',
            error_code: 'parameter_required',
            message: 'job_id参数缺失'
          }],
          warnings: []
        });
      }
      
      logger.info(`[${requestId}] 开始提交V2数据，任务ID: ${job_id}`);
      
      // 检查feature flag
      if (!process.env.IMPORT_V2 || process.env.IMPORT_V2 !== 'true') {
        return res.status(404).json({
          success: false,
          phase: 'commit',
          message: 'V2功能未启用',
          summary: { inserted: 0, updated: 0, skipped: 0, error_count: 1 },
          errors: [{
            sheet: 'System',
            row_number: 0,
            field: 'feature_flag',
            value: process.env.IMPORT_V2 || 'undefined',
            error_code: 'feature_disabled',
            message: 'V2功能未启用'
          }],
          warnings: []
        });
      }

      const job = await ImportJob.findByJobId(job_id);
      if (!job) {
        return res.status(404).json({
          success: false,
          phase: 'commit',
          message: '任务不存在',
          summary: { inserted: 0, updated: 0, skipped: 0, error_count: 1 },
          errors: [{
            sheet: 'System',
            row_number: 0,
            field: 'job_id',
            value: job_id,
            error_code: 'job_not_found',
            message: '任务不存在'
          }],
          warnings: []
        });
      }

      if (job.status !== 'validated') {
        return res.status(400).json({
          success: false,
          phase: 'commit',
          message: `任务状态不正确，当前状态: ${job.status}`,
          summary: { inserted: 0, updated: 0, skipped: 0, error_count: 1 },
          errors: [{
            sheet: 'System',
            row_number: 0,
            field: 'status',
            value: job.status,
            error_code: 'invalid_status',
            message: `任务状态不正确，当前状态: ${job.status}`
          }],
          warnings: []
        });
      }

      // 执行提交
      const result = await this.importService.commitV2(job, requestId);
      
      // 检查是否有错误
      if (result.errors && result.errors.length > 0) {
        logger.warn(`[${requestId}] 提交V2数据部分失败，任务ID: ${job_id}，错误数: ${result.errors.length}`);
        
        await this.logAuditAction(req, 'commit_v2', {
          job_id: job.job_id,
          request_id: requestId,
          error_count: result.errors.length,
          summary: result.summary
        }, 'partial_failure');

        return res.json({
          success: false,
          phase: 'commit',
          message: `提交完成，但有 ${result.errors.length} 个错误`,
          summary: result.summary,
          errors: result.errors,
          warnings: result.warnings || []
        });
      }
      
      await this.logAuditAction(req, 'commit_v2', {
        job_id: job.job_id,
        request_id: requestId,
        summary: result.summary
      }, 'success');

      res.json({
        success: true,
        phase: 'commit',
        message: '数据提交成功',
        summary: result.summary,
        errors: [],
        warnings: result.warnings || []
      });
    } catch (error) {
      logger.error(`[${requestId}] 提交V2数据失败:`, error);
      await this.logAuditAction(req, 'commit_v2', { error: error.message, request_id: requestId }, 'error');
      
      // 分类数据库错误
      let errorCode = 'system_error';
      let errorMessage = error.message;
      
      if (error.code) {
        switch (error.code) {
          case '23505': // PostgreSQL unique violation
          case 'ER_DUP_ENTRY': // MySQL duplicate entry
            errorCode = 'unique_violation';
            break;
          case '23502': // PostgreSQL not null violation
          case 'ER_BAD_NULL_ERROR': // MySQL not null violation
            errorCode = 'not_null_violation';
            break;
          case '23503': // PostgreSQL foreign key violation
          case 'ER_NO_REFERENCED_ROW': // MySQL foreign key violation
            errorCode = 'foreign_key_violation';
            break;
          case '22001': // PostgreSQL string data right truncation
          case 'ER_DATA_TOO_LONG': // MySQL data too long
            errorCode = 'data_truncation';
            break;
          default:
            errorCode = 'database_error';
        }
      }
      
      res.status(500).json({
        success: false,
        phase: 'commit',
        message: '提交失败',
        summary: { inserted: 0, updated: 0, skipped: 0, error_count: 1 },
        errors: [{
          sheet: 'DB',
          row_number: 0,
          field: 'database',
          value: '',
          error_code: errorCode,
          message: errorMessage
        }],
        warnings: []
      });
    }
  }

  /**
   * 查询V2导入任务状态
   */
  async getJobStatusV2(req, res) {
    const requestId = req.headers['x-request-id'] || 'req_' + Date.now();
    res.setHeader('X-Request-Id', requestId);
    res.setHeader('Content-Type', 'application/json');
    
    try {
      const { id } = req.params;
      logger.info(`[${requestId}] 查询V2任务状态: ${id}`);
      
      if (!process.env.IMPORT_V2 || process.env.IMPORT_V2 !== 'true') {
        return res.status(404).json({ 
          success: false, 
          phase: 'system',
          status: 'failed',
          jobId: id,
          summary: { inserted: 0, updated: 0, skipped: 0, error_count: 0 },
          errors: [],
          warnings: [],
          system_error: 'V2功能未启用',
          requestId: requestId
        });
      }

      const job = await ImportJob.findByJobId(id);
      if (!job) {
        return res.status(404).json({ 
          success: false, 
          phase: 'system',
          status: 'not_found',
          jobId: id,
          summary: { inserted: 0, updated: 0, skipped: 0, error_count: 0 },
          errors: [],
          warnings: [],
          system_error: '任务不存在',
          requestId: requestId
        });
      }

      // 解析存储的错误和摘要信息
       let errors = [];
       let summary = { inserted: 0, updated: 0, skipped: 0, error_count: 0 };
       let phase = 'validate';
       
       try {
         if (job.validation_errors) {
           let validationData;
           if (typeof job.validation_errors === 'string') {
             // 尝试解析JSON字符串
             try {
               validationData = JSON.parse(job.validation_errors);
             } catch (jsonError) {
               logger.warn(`[${requestId}] JSON解析失败，尝试清理数据:`, jsonError.message);
               // 如果JSON解析失败，可能是路径字符串，跳过解析
               validationData = null;
             }
           } else {
             validationData = job.validation_errors;
           }
           
           if (validationData && typeof validationData === 'object') {
             if (validationData.errors && Array.isArray(validationData.errors)) {
               errors = validationData.errors;
             }
             if (validationData.summary && typeof validationData.summary === 'object') {
               summary = { ...summary, ...validationData.summary };
             }
             if (validationData.phase) {
               phase = validationData.phase;
             }
           }
         }
       } catch (parseError) {
         logger.warn(`[${requestId}] 解析任务错误信息失败:`, parseError);
       }
      
      // 更新错误计数
      summary.error_count = errors.length;
      
      // 判断任务是否成功
      const isSuccess = job.status === 'completed' || job.status === 'validated';
      const finalStatus = job.status === 'completed' ? 'succeeded' : 
                         job.status === 'failed' ? 'failed' : 
                         job.status === 'validated' ? 'validated' : 
                         job.status;

      const baseDir = path.join(path.dirname(job.file_path));
      const files = {
        validate_csv: path.join(baseDir, `validate_report_${job.job_id}.csv`),
        validate_json: path.join(baseDir, `validation_report_${job.job_id}.json`),
        normalization_json: path.join(baseDir, `normalization_log_${job.job_id}.json`),
        category_upsert_json: path.join(baseDir, `category_upsert_${job.job_id}.json`),
        commit_csv: path.join(baseDir, `commit_report_${job.job_id}.csv`)
      };

      const exist = async (p) => {
        try { await fs.access(p); return true; } catch { return false; }
      };

      // 构建统一的响应结构
      const response = {
        success: isSuccess,
        phase: phase,
        status: finalStatus,
        jobId: job.job_id,
        summary: summary,
        errors: errors,
        warnings: [],
        error_report_url: summary.error_count > 0 ? `/api/admin/menu-import/v2/jobs/${job.job_id}/validate-report` : null,
        requestId: requestId,
        // 额外的任务信息
        job_details: {
          id: job.id,
          import_type: job.import_type,
          file_path: job.file_path,
          file_size: job.file_size,
          total_rows: job.total_rows || 0,
          valid_rows: job.valid_rows || 0,
          error_rows: job.error_rows || 0,
          warning_rows: job.warning_rows || 0,
          new_records: job.new_records || 0,
          updated_records: job.updated_records || 0,
          completed_at: job.completed_at,
          created_at: job.created_at,
          updated_at: job.updated_at,
          can_rollback: job.status === 'completed' && job.snapshot_data,
          has_snapshot: !!job.snapshot_data,
          validation_report_url: (await exist(files.validate_json)) ? `/api/admin/menu-import/v2/jobs/${job.job_id}/validation-report.json` : null,
          normalization_log_url: (await exist(files.normalization_json)) ? `/api/admin/menu-import/v2/jobs/${job.job_id}/normalization-log` : null,
          category_upsert_url: (await exist(files.category_upsert_json)) ? `/api/admin/menu-import/v2/jobs/${job.job_id}/category-upsert` : null,
          commit_report_url: (await exist(files.commit_csv)) ? `/api/admin/menu-import/v2/jobs/${job.job_id}/commit-report` : null
        }
      };
      
      // 如果是系统错误且没有具体错误信息，添加系统错误字段
      if (!isSuccess && errors.length === 0) {
        response.system_error = job.error_message || '任务处理失败';
      }

      res.json(response);
    } catch (error) {
      logger.error(`[${requestId}] 查询V2任务状态失败:`, error);
      
      // 确保即使发生系统错误也返回统一的JSON结构
      res.status(500).json({
        success: false,
        phase: 'system',
        status: 'failed',
        jobId: req.params.id || 'unknown',
        summary: { inserted: 0, updated: 0, skipped: 0, error_count: 0 },
        errors: [],
        warnings: [],
        system_error: 'Unexpected server error, see logs with requestId',
        error_report_url: null,
        requestId: requestId,
        job_details: null
      });
    }
  }

  async downloadNormalizationLogV2(req, res) {
    try {
      const { job_id } = req.params;
      if (!process.env.IMPORT_V2 || process.env.IMPORT_V2 !== 'true') {
        return res.status(404).json({ success: false, message: 'V2功能未启用' });
      }
      const job = await ImportJob.findByJobId(job_id);
      if (!job) return res.status(404).json({ success: false, message: '任务不存在' });
      const file = path.join(path.dirname(job.file_path), `normalization_log_${job.job_id}.json`);
      try { await fs.access(file); } catch { return res.status(404).json({ success:false, message:'文件不存在' }); }
      res.setHeader('Content-Type', 'application/json');
      res.setHeader('Content-Disposition', `attachment; filename="normalization_log_${job_id}.json"`);
      require('fs').createReadStream(file).pipe(res);
    } catch (error) {
      logger.error('下载V2规范化日志失败:', error);
      res.status(500).json({ success:false, message:'下载失败', error: error.message });
    }
  }

  async downloadCategoryUpsertV2(req, res) {
    try {
      const { job_id } = req.params;
      if (!process.env.IMPORT_V2 || process.env.IMPORT_V2 !== 'true') {
        return res.status(404).json({ success: false, message: 'V2功能未启用' });
      }
      const job = await ImportJob.findByJobId(job_id);
      if (!job) return res.status(404).json({ success: false, message: '任务不存在' });
      const file = path.join(path.dirname(job.file_path), `category_upsert_${job.job_id}.json`);
      try { await fs.access(file); } catch { return res.status(404).json({ success:false, message:'文件不存在' }); }
      res.setHeader('Content-Type', 'application/json');
      res.setHeader('Content-Disposition', `attachment; filename="category_upsert_${job_id}.json"`);
      require('fs').createReadStream(file).pipe(res);
    } catch (error) {
      logger.error('下载V2分类Upsert产物失败:', error);
      res.status(500).json({ success:false, message:'下载失败', error: error.message });
    }
  }

  /**
   * 下载V2提交报告
   */
  async downloadCommitReportV2(req, res) {
    try {
      const { job_id } = req.params;
      logger.info(`下载V2提交报告: ${job_id}`);
      
      // 检查feature flag
      if (!process.env.IMPORT_V2 || process.env.IMPORT_V2 !== 'true') {
        return res.status(404).json({
          success: false,
          message: 'V2功能未启用'
        });
      }

      const job = await ImportJob.findByJobId(job_id);
      if (!job) {
        return res.status(404).json({
          success: false,
          message: '任务不存在'
        });
      }

      const reportPath = path.join(path.dirname(job.file_path), `commit_report_${job.job_id}.csv`);
      
      try {
        await fs.access(reportPath);
      } catch (e) {
        return res.status(404).json({
          success: false,
          message: '提交报告文件不存在'
        });
      }

      res.setHeader('Content-Type', 'text/csv');
      res.setHeader('Content-Disposition', `attachment; filename="commit_report_${job_id}.csv"`);
      
      const fileStream = require('fs').createReadStream(reportPath);
      fileStream.pipe(res);
      
      await this.logAuditAction(req, 'download_commit_report_v2', { job_id }, 'success');
    } catch (error) {
      logger.error('下载V2提交报告失败:', error);
      res.status(500).json({
        success: false,
        message: '下载提交报告失败',
        error: error.message
      });
    }
  }

  // 新增：下载V2验证报告
  async downloadValidateReportV2(req, res) {
    try {
      const { job_id } = req.params;
      logger.info(`下载V2验证报告: ${job_id}`);

      // 检查feature flag
      if (!process.env.IMPORT_V2 || process.env.IMPORT_V2 !== 'true') {
        return res.status(404).json({ success: false, message: 'V2功能未启用' });
      }

      const job = await ImportJob.findByJobId(job_id);
      if (!job) {
        return res.status(404).json({ success: false, message: '任务不存在' });
      }

      const reportPath = path.join(path.dirname(job.file_path), `validate_report_${job.job_id}.csv`);
      try {
        await fs.access(reportPath);
      } catch (e) {
        return res.status(404).json({ success: false, message: '验证报告文件不存在' });
      }

      res.setHeader('Content-Type', 'text/csv');
      res.setHeader('Content-Disposition', `attachment; filename="validate_report_${job_id}.csv"`);
      const fileStream = require('fs').createReadStream(reportPath);
      fileStream.pipe(res);

      await this.logAuditAction(req, 'download_validate_report_v2', { job_id }, 'success');
    } catch (error) {
      logger.error('下载V2验证报告失败:', error);
      res.status(500).json({ success: false, message: '下载验证报告失败', error: error.message });
    }
  }

  /**
   * 下载V2任务日志
   */
  async downloadJobLogV2(req, res) {
    try {
      const { job_id } = req.params;
      logger.info(`下载V2任务日志: ${job_id}`);
      
      // 检查feature flag
      if (!process.env.IMPORT_V2 || process.env.IMPORT_V2 !== 'true') {
        return res.status(404).json({
          success: false,
          message: 'V2功能未启用'
        });
      }

      const job = await ImportJob.findByJobId(job_id);
      if (!job) {
        return res.status(404).json({
          success: false,
          message: '任务不存在'
        });
      }

      const logPath = path.join(path.dirname(job.file_path), `job_log_${job.job_id}.txt`);
      
      try {
        await fs.access(logPath);
      } catch (e) {
        return res.status(404).json({
          success: false,
          message: '任务日志文件不存在'
        });
      }

      res.setHeader('Content-Type', 'text/plain');
      res.setHeader('Content-Disposition', `attachment; filename="job_log_${job_id}.txt"`);
      
      const fileStream = require('fs').createReadStream(logPath);
      fileStream.pipe(res);
      
      await this.logAuditAction(req, 'download_job_log_v2', { job_id }, 'success');
    } catch (error) {
      logger.error('下载V2任务日志失败:', error);
      res.status(500).json({
        success: false,
        message: '下载任务日志失败',
        error: error.message
      });
    }
  }

  /**
   * 图片批量导入和分配
   */
  async batchAssignImages(req, res) {
    // 配置多文件上传
    const imageUpload = multer({
      storage: multer.diskStorage({
        destination: async (req, file, cb) => {
          const uploadDir = path.join(process.cwd(), 'uploads', 'images', 'batch');
          await fs.mkdir(uploadDir, { recursive: true });
          cb(null, uploadDir);
        },
        filename: (req, file, cb) => {
          const uniqueName = `${Date.now()}_${uuidv4()}_${file.originalname}`;
          cb(null, uniqueName);
        }
      }),
      limits: {
        fileSize: 5 * 1024 * 1024, // 5MB per image
        files: 100 // 最多100个文件
      },
      fileFilter: (req, file, cb) => {
        if (file.fieldname === 'images') {
          // 图片文件验证
          const allowedImageTypes = ['image/jpeg', 'image/png', 'image/webp'];
          if (allowedImageTypes.includes(file.mimetype)) {
            cb(null, true);
          } else {
            cb(new Error('只支持 JPEG, PNG, WebP 格式的图片文件'), false);
          }
        } else if (file.fieldname === 'mapping_file') {
          // CSV映射文件验证
          if (file.mimetype === 'text/csv' || file.originalname.endsWith('.csv')) {
            cb(null, true);
          } else {
            cb(new Error('映射文件只支持 CSV 格式'), false);
          }
        } else {
          cb(new Error('未知的文件字段'), false);
        }
      }
    }).fields([
      { name: 'images', maxCount: 100 },
      { name: 'mapping_file', maxCount: 1 }
    ]);

    imageUpload(req, res, async (err) => {
      if (err) {
        logger.error('图片批量上传失败:', err);
        return res.status(400).json({
          success: false,
          message: '文件上传失败',
          error: err.message
        });
      }

      try {
        logger.info('开始图片批量导入和分配');
        
        // 检查feature flag
        if (!process.env.IMPORT_V2 || process.env.IMPORT_V2 !== 'true') {
          return res.status(404).json({
            success: false,
            message: 'V2功能未启用'
          });
        }

        const images = req.files?.images || [];
        const mappingFile = req.files?.mapping_file?.[0];

        if (images.length === 0) {
          return res.status(400).json({
            success: false,
            message: '请至少上传一张图片'
          });
        }

        // 创建批量导入任务
        const job = await ImportJob.create({
          import_type: 'image_batch',
          original_filename: `batch_images_${images.length}_files`,
          file_path: images[0].destination, // 使用第一个图片的目录作为任务路径
          file_size: images.reduce((total, img) => total + img.size, 0),
          status: 'processing'
        });

        // 执行图片批量分配
        const result = await this.processImageBatchAssignment(job, images, mappingFile);
        
        await this.logAuditAction(req, 'batch_assign_images', {
          job_id: job.job_id,
          image_count: images.length,
          has_mapping_file: !!mappingFile
        }, 'success');

        res.json({
          success: true,
          job_id: job.job_id,
          processed_count: result.processed_count,
          matched_count: result.matched_count,
          report_url: `/api/admin/v2/jobs/${job.job_id}/commit-report`
        });
      } catch (error) {
        logger.error('图片批量导入和分配失败:', error);
        await this.logAuditAction(req, 'batch_assign_images', { error: error.message }, 'error');
        res.status(500).json({
          success: false,
          message: '图片批量分配失败',
          error: error.message
        });
      }
    });
  }

  /**
   * 处理图片批量分配逻辑
   */
  async processImageBatchAssignment(job, images, mappingFile) {
    const { query: dbQuery } = require('../config/database');
    const csv = require('csv-parser');
    const createReadStream = require('fs').createReadStream;
    
    try {
      let mappingData = new Map();
      
      // 如果有映射文件，解析映射关系
      if (mappingFile) {
        const mappingResults = [];
        await new Promise((resolve, reject) => {
          createReadStream(mappingFile.path)
            .pipe(csv())
            .on('data', (data) => mappingResults.push(data))
            .on('end', resolve)
            .on('error', reject);
        });
        
        // 构建映射关系 (图片文件名 -> dish_sku)
        mappingResults.forEach(row => {
          const imageFile = row.image_file || row.filename;
          const dishSku = row.dish_sku || row.sku;
          if (imageFile && dishSku) {
            mappingData.set(imageFile.toLowerCase(), dishSku);
          }
        });
      }
      
      // 获取所有菜品数据用于匹配
      const dishes = await dbQuery(`
        SELECT id, dish_sku, dish_slug, name_en, name 
        FROM dishes 
        WHERE deleted_at IS NULL
      `);
      
      const dishMap = new Map();
      dishes.forEach(dish => {
        // 按优先级建立索引：dish_sku > dish_slug > name_en
        if (dish.dish_sku) dishMap.set(dish.dish_sku.toLowerCase(), dish);
        if (dish.dish_slug) dishMap.set(dish.dish_slug.toLowerCase(), dish);
        if (dish.name_en) dishMap.set(dish.name_en.toLowerCase().replace(/[^a-z0-9]/g, ''), dish);
      });
      
      let processedCount = 0;
      let matchedCount = 0;
      const reportData = [];
      
      // 处理每个图片文件
      for (const image of images) {
        const originalName = path.parse(image.originalname).name.toLowerCase();
        let matchedDish = null;
        let matchMethod = 'none';
        
        // 1. 优先使用映射文件
        if (mappingData.has(image.originalname.toLowerCase())) {
          const dishSku = mappingData.get(image.originalname.toLowerCase());
          matchedDish = dishMap.get(dishSku.toLowerCase());
          matchMethod = 'mapping_file';
        }
        
        // 2. 按文件名匹配 dish_sku
        if (!matchedDish && dishMap.has(originalName)) {
          matchedDish = dishMap.get(originalName);
          matchMethod = 'dish_sku';
        }
        
        // 3. 按文件名匹配 dish_slug
        if (!matchedDish) {
          for (const [key, dish] of dishMap.entries()) {
            if (dish.dish_slug && dish.dish_slug.toLowerCase() === originalName) {
              matchedDish = dish;
              matchMethod = 'dish_slug';
              break;
            }
          }
        }
        
        // 4. 按文件名匹配 name_en（去除特殊字符）
        if (!matchedDish) {
          const cleanName = originalName.replace(/[^a-z0-9]/g, '');
          for (const [key, dish] of dishMap.entries()) {
            if (dish.name_en && dish.name_en.toLowerCase().replace(/[^a-z0-9]/g, '') === cleanName) {
              matchedDish = dish;
              matchMethod = 'name_en';
              break;
            }
          }
        }
        
        processedCount++;
        
        if (matchedDish) {
          // 移动图片到正式目录
          const finalImageDir = path.join(process.cwd(), 'public', 'images', 'menu');
          await fs.mkdir(finalImageDir, { recursive: true });
          
          const finalImagePath = path.join(finalImageDir, image.filename);
          await fs.rename(image.path, finalImagePath);
          
          // 更新数据库中的image_url
          const imageUrl = `/images/menu/${image.filename}`;
          await dbQuery(
            'UPDATE dishes SET image_url = ?, updated_at = NOW() WHERE id = ?',
            [imageUrl, matchedDish.id]
          );
          
          matchedCount++;
          
          reportData.push({
            image_file: image.originalname,
            dish_sku: matchedDish.dish_sku,
            dish_name: matchedDish.name || matchedDish.name_en,
            match_method: matchMethod,
            image_url: imageUrl,
            status: 'success'
          });
        } else {
          reportData.push({
            image_file: image.originalname,
            dish_sku: '',
            dish_name: '',
            match_method: 'none',
            image_url: '',
            status: 'no_match'
          });
        }
      }
      
      // 生成CSV报告
      const reportPath = path.join(path.dirname(images[0].path), `image_batch_report_${job.job_id}.csv`);
      const csvContent = [
        'Image File,Dish SKU,Dish Name,Match Method,Image URL,Status',
        ...reportData.map(row => 
          `"${row.image_file}","${row.dish_sku}","${row.dish_name}","${row.match_method}","${row.image_url}","${row.status}"`
        )
      ].join('\n');
      
      await fs.writeFile(reportPath, csvContent, 'utf8');
      
      // 更新任务状态
      await job.updateStatus('completed', {
        processed_count: processedCount,
        matched_count: matchedCount,
        report_path: reportPath
      });
      
      logger.info(`图片批量分配完成: 处理${processedCount}个文件，匹配${matchedCount}个`);
      
      return {
        processed_count: processedCount,
        matched_count: matchedCount
      };
    } catch (error) {
      await job.updateStatus('failed', { error_message: error.message });
      throw error;
    }
  }
}

module.exports = new MenuImportController();